﻿using HDSmartCommunity.BUS;
using HDSmartCommunity.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace HDSmartCommunity.DAL
{
    public class ReportDAL
    {
        public List<Incarevents> QueryIncarevents(Incarevents incarevents)
        {
            try
            {
                string strSql = "SELECT a.* ,datetime(cmdtime,'localtime','-8 hours')as t1,b.operatename,c.typename from incarevents as a LEFT JOIN operatetype as b on a.operatetype=b.ID LEFT join credencetype as c on a.credenctype=c.ID";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(incarevents.cmdtime))
                {
                    sqlwhere += "t1>'" + incarevents.cmdtime + "' and";
                }
                if (!string.IsNullOrEmpty(incarevents.UUID))
                {
                    sqlwhere += "like '%" + incarevents.UUID + "%' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (incarevents.Recordhead > -1 && incarevents.Recordtail > 0)
                {
                    strSql += " limit " + incarevents.Recordhead + "," + incarevents.Recordtail;
                }

                CommFN<Incarevents> fn = new CommFN<Incarevents>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }

        public int QueryIncareventsCount(Incarevents incarevents)
        {
            try
            {
                string strSql = "SELECT count(1),datetime(cmdtime,'localtime','-8 hours')as t1 from incarevents";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(incarevents.cmdtime))
                {
                    sqlwhere += "t1>'" + incarevents.cmdtime + "' and";
                }
                if (!string.IsNullOrEmpty(incarevents.UUID))
                {
                    sqlwhere += "like '%" + incarevents.UUID + "%' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                CommFN<Incarevents> fn = new CommFN<Incarevents>();
                return Convert.ToInt32(fn.ExecuteScalar(strSql));
            }
            catch
            {
                return 0;
            }
        }

        public List<Outcarevents> QueryOutcarevents(Outcarevents outcarevents)
        {
            try
            {
                string strSql = "SELECT *,datetime(cmdtime,'localtime','-8 hours')as t1 from outcarevents";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(outcarevents.cmdtime))
                {
                    sqlwhere += "t1>'" + outcarevents.cmdtime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (outcarevents.Recordhead > -1 && outcarevents.Recordtail > 0)
                {
                    strSql += " limit " + outcarevents.Recordhead + "," + outcarevents.Recordtail;
                }

                CommFN<Outcarevents> fn = new CommFN<Outcarevents>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }

        public int QueryOutcareventsCount(Outcarevents outcarevents)
        {
            try
            {
                string strSql = "SELECT count(1),datetime(cmdtime,'localtime','-8 hours')as t1 from outcarevents";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(outcarevents.cmdtime))
                {
                    sqlwhere += "t1>'" + outcarevents.cmdtime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                CommFN<Incarevents> fn = new CommFN<Incarevents>();
                return Convert.ToInt32(fn.ExecuteScalar(strSql));
            }
            catch
            {
                return 0;
            }
        }

        public List<Equipment> QueryEquipment(Equipment equipment)
        {
            try
            {
                string strSql = "SELECT *,datetime(actiontime,'localtime','-8 hours')as t1 from equipment_operation";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(equipment.actiontime))
                {
                    sqlwhere += "t1>'" + equipment.actiontime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (equipment.Recordhead > -1 && equipment.Recordtail > 0)
                {
                    strSql += " limit " + equipment.Recordhead + "," + equipment.Recordtail;
                }

                CommFN<Equipment> fn = new CommFN<Equipment>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }

        public int QueryEquipmentCount(Equipment equipment)
        {
            try
            {
                string strSql = "SELECT count(1),datetime(actiontime,'localtime','-8 hours')as t1 from equipment_operation";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(equipment.actiontime))
                {
                    sqlwhere += "t1>'" + equipment.actiontime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                CommFN<Equipment> fn = new CommFN<Equipment>();
                return Convert.ToInt32(fn.ExecuteScalar(strSql));
            }
            catch
            {
                return 0;
            }
        }

        public List<ModeRecord> QueryModeRecord(ModeRecord modeRecord)
        {
            try
            {
                string strSql = "SELECT *,datetime(updatetime,'localtime','-8 hours')as t1 from workmoderecord";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(modeRecord.updatetime))
                {
                    sqlwhere += "t1>'" + modeRecord.updatetime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (modeRecord.Recordhead > -1 && modeRecord.Recordtail > 0)
                {
                    strSql += " limit " + modeRecord.Recordhead + "," + modeRecord.Recordtail;
                }

                CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }

        public int QueryModeRecordCount(ModeRecord modeRecord)
        {
            try
            {
                string strSql = "SELECT *,datetime(updatetime,'localtime','-8 hours')as t1 from workmoderecord";
                string sqlwhere = string.Empty;

                if (!string.IsNullOrEmpty(modeRecord.updatetime))
                {
                    sqlwhere += "t1>'" + modeRecord.updatetime + "' and";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
                return Convert.ToInt32(fn.ExecuteScalar(strSql));
            }
            catch
            {
                return 0;
            }
        }

        public bool DeleteModeRecord()
        {
            string strSql = string.Format("DELETE FROM workmoderecord WHERE datetime(updatetime,'localtime','-8 hours')<'{0}'", DateTime.Now.Date.AddDays(-30).ToString("yyyy-MM-dd"));
            //LogHelper.Log(strSql);
            CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
            return fn.ExecFN(strSql);
        }

        public bool DeleteEquipment()
        {
            string strSql = string.Format("DELETE FROM equipment_operation WHERE datetime(actiontime,'localtime','-8 hours')<'{0}'", DateTime.Now.Date.AddDays(-30).ToString("yyyy-MM-dd"));
            //LogHelper.Log(strSql);
            CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
            return fn.ExecFN(strSql);
        }

        public bool DeleteIncarevents()
        {
            string strSql = string.Format("DELETE FROM outcarevents WHERE datetime(cmdtime,'localtime','-8 hours')<'{0}'", DateTime.Now.Date.AddDays(-30).ToString("yyyy-MM-dd"));
            CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
            return fn.ExecFN(strSql);
        }

        public bool DeleteOutcarevents()
        {
            string strSql = string.Format("DELETE FROM incarevents WHERE datetime(cmdtime,'localtime','-8 hours')<'{0}'", DateTime.Now.Date.AddDays(-30).ToString("yyyy-MM-dd"));
            CommFN<ModeRecord> fn = new CommFN<ModeRecord>();
            return fn.ExecFN(strSql);
        }
    }
}
